/*   ro-npcs, a plugin for the Rolgps, part of the RolGPS Proyect
 *   Copyright (C) 2009 Pablo Alejandro Costesich <rhfixer@gmail.com>
 *
 *   This program is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   This program is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 */

package dataTypes;

import databaseHandler.Database;
import deployers.sqlite.Deployer;
import deployers.sqlite.Field;
import deployers.sqlite.Table;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author xeph
 */
public class DataSource extends Database {

    public DataSource(File databaseFile) {
        this.setDatabaseFile(databaseFile);
    }

    public Profesion[] getProfesiones() {
        Profesion[] result = new Profesion[1];
        String query = "SELECT id, name FROM profesiones ORDER BY name ASC;";
        try {
            Object[][] resultado = executeStatement(query);
            result = new Profesion[resultado.length+1];
            result[0]=new Profesion();
            result[0].setId("");
            result[0].setName("");
            int iterator = 1;
            for (Object[] prof : resultado) {
                result[iterator]=new Profesion();
                result[iterator].setId(prof[0].toString());
                result[iterator].setName(prof[1].toString());
                iterator++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataSource.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    private Profesion getProfesion(String id) {
        String query = "SELECT id, name FROM profesiones WHERE id=" + escapeSql(id) + " LIMIT 1;";
        Profesion result = new Profesion();
        try {
            Object[][] tempResult = executeStatement(query);
            if (tempResult.length < 1) {
                //throw new SQLException("Not enough results");
                result.setId(id);
                result.setName("N/D");
            }
            result.setId(tempResult[0][0].toString());
            result.setName(tempResult[0][1].toString());

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public Reino[] getReinos() {
        Reino[] result = new Reino[1];
        String query = "SELECT id, name FROM reinos ORDER BY name ASC;";
        try {
            Object[][] resultado = executeStatement(query);
            result = new Reino[resultado.length+1];
            result[0]=new Reino();
            result[0].setId("");
            result[0].setName("");
            int iterator = 1;
            for (Object[] prof : resultado) {
                result[iterator]=new Reino();
                result[iterator].setId(prof[0].toString());
                result[iterator].setName(prof[1].toString());
                iterator++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataSource.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    private Reino getReino(String id) {
        String query = "SELECT id, name FROM reinos WHERE id=" + escapeSql(id) + " LIMIT 1;";
        Reino result = new Reino();
        try {
            Object[][] tempResult = executeStatement(query);
            if (tempResult.length < 1) {
                //throw new SQLException("Not enough results");
                result.setId(id);
                result.setName("N/D");
            }
            result.setId(tempResult[0][0].toString());
            result.setName(tempResult[0][1].toString());

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public Ciudad[] getCiudades() {
        Ciudad[] result = new Ciudad[1];
        String query = "SELECT id, name FROM ciudades ORDER BY name ASC;";
        try {
            Object[][] resultado = executeStatement(query);
            result = new Ciudad[resultado.length+1];
            result[0]=new Ciudad();
            result[0].setId("");
            result[0].setName("");
            int iterator = 1;
            for (Object[] prof : resultado) {
                result[iterator] = new Ciudad();
                result[iterator].setId(prof[0].toString());
                result[iterator].setName(prof[1].toString());
                iterator++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataSource.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    private Ciudad getCiudad(String id) {
        String query = "SELECT id, name FROM ciudades WHERE id=" + escapeSql(id) + " LIMIT 1;";
        Ciudad result = new Ciudad();
        try {
            Object[][] tempResult = executeStatement(query);
            if (tempResult.length < 1) {
                //throw new SQLException("Not enough results");
                result.setId(id);
                result.setName("N/D");
            }
            result.setId(tempResult[0][0].toString());
            result.setName(tempResult[0][1].toString());

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public Npc[] buscarNpc(Npc modelo) {

        Npc[] result = new Npc[1];

        StringBuffer query = new StringBuffer();
        query.append(
                "SELECT" +
                " id," +
                " name," +
                " profesionid," +
                " ciudadid," +
                " reinoid," +
                " x," +
                " y," +
                " z," +
                " userid" +
                " FROM npcs" +
                " WHERE 1=1 ");

        //Requisitos
        if (!modelo.getId().isEmpty()) {
            query.append(" AND id=" + escapeSql(modelo.getId()));
        }
        if (!modelo.getName().isEmpty()) {
            query.append(" AND name like \'%" + escapeSql(modelo.getName()) + "%\'");
        }
        if (!modelo.getProfesion().getId().isEmpty()) {
            query.append(" AND profesionid = " + escapeSql(modelo.getProfesion().getId()));
        }
        if (!modelo.getCiudad().getId().isEmpty()) {
            query.append(" AND ciudadid=" + escapeSql(modelo.getCiudad().getId()));
        }
        if (!modelo.getReino().getId().isEmpty()) {
            query.append(" AND reinoid=" + escapeSql(modelo.getReino().getId()));
        }
        if (modelo.getLugar().getX() != -1) {
            query.append(" AND x=" + escapeSql(String.valueOf(modelo.getLugar().getX())));
        }
        if (modelo.getLugar().getY() != -1) {
            query.append(" AND y=" + escapeSql(String.valueOf(modelo.getLugar().getY())));
       
        }
        if (modelo.getLugar().getZ() != -1) {
            query.append(" AND z=" + escapeSql(String.valueOf(modelo.getLugar().getZ())));
        }
        if (!modelo.getUserId().isEmpty()) {
            query.append(" AND userid=" + escapeSql(modelo.getUserId()));
        }

        query.append(";");
        try {
            //System.out.println(query.toString());
            Object[][] temporalResult = this.executeStatement(query.toString());
            result = new Npc[temporalResult.length];
            int iterator = 0;
            for (Object[] eachResult : temporalResult) {
                result[iterator]=new Npc();
                result[iterator].setId(eachResult[0].toString());
                result[iterator].setName(eachResult[1].toString());
                result[iterator].setProfesion(this.getProfesion(eachResult[2].toString()));
                result[iterator].setCiudad(this.getCiudad(eachResult[3].toString()));
                result[iterator].setReino(this.getReino(eachResult[4].toString()));

                Lugar coords = new Lugar();
                coords.setX(Integer.parseInt(eachResult[5].toString()));
                coords.setY(Integer.parseInt(eachResult[6].toString()));
                coords.setZ(Integer.parseInt(eachResult[7].toString()));
                result[iterator].setLugar(coords);

                result[iterator].setUserId(eachResult[8].toString());

                iterator++;
            }
        } catch (SQLException e) {
            System.err.println("ERROR EN LA CONSULTA DE BUSQUEDA.\nConsulta:"+query.toString());
            result = new Npc[1];
        }
        return result;
    }

    public void modificarNpc(Npc modelo) throws Exception {
        StringBuffer query = new StringBuffer();
        query.append("UPDATE npcs SET ");
        boolean hasPrevious = false;
        //Requisitos
        if (!modelo.getName().isEmpty()) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" name = \"" + escapeSql(modelo.getName()) + "\"");
        }
        if (!modelo.getProfesion().getId().isEmpty()) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" profesionid = " + escapeSql(modelo.getProfesion().getId()));
        }
        if (!modelo.getCiudad().getId().isEmpty()) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" ciudadid=" + escapeSql(modelo.getCiudad().getId()));
        }
        if (!modelo.getReino().getId().isEmpty()) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" reinoid=" + escapeSql(modelo.getReino().getId()));
        }
        if (modelo.getLugar().getX() != -1) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" x=" + escapeSql(String.valueOf(modelo.getLugar().getX())));
        }
        if (modelo.getLugar().getY() != -1) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" y=" + escapeSql(String.valueOf(modelo.getLugar().getY())));

        }
        if (modelo.getLugar().getZ() != -1) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" z="  + escapeSql(String.valueOf(modelo.getLugar().getZ())));

        }
        if (!modelo.getUserId().isEmpty()) {
            if (hasPrevious) {
                query.append(",");
            }
            hasPrevious = true;
            query.append(" userid=" + escapeSql(modelo.getUserId()));

        }
        if (modelo.getId().isEmpty()) {
            throw new Exception("falta el id del objeto a modificar");
        } else {
            query.append(" WHERE id=" + modelo.getId() + ";");
        }
        
        System.err.println(query.toString());
        try {
            executeStatement(query.toString());
        } catch (SQLException ex) {
            Logger.getLogger(DataSource.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void agregarNpc(Npc modelo) {
        StringBuffer query = new StringBuffer();

        query.append("INSERT into npcs(" +
                "id, " +
                "name, " +
                "profesionid, " +
                "ciudadid, " +
                "reinoid, " +
                "x, " +
                "y, " +
                "z, " +
                "userid" +
                ")" +
                " VALUES(null,");

        //I should solve the little problem of adding
        //null fields... it breakes the query.
        query.append(
                "\"" + escapeSql(modelo.getName()) + "\", " +
                "\"" + escapeSql(modelo.getProfesion().getId()) + "\", " +
                "\"" + escapeSql(modelo.getCiudad().getId())  + "\", " +
                "\"" + escapeSql(modelo.getReino().getId())  + "\", " +
                "\"" + escapeSql(String.valueOf(modelo.getLugar().getX()))  + "\", " +
                "\"" + escapeSql(String.valueOf(modelo.getLugar().getY()))  + "\", " +
                "\"" + escapeSql(String.valueOf(modelo.getLugar().getZ()))  + "\", " +
                "\"" + escapeSql(modelo.getUserId()) + "\" ");

        query.append(");");

        try {
            executeStatement(query.toString());
        } catch (SQLException ex) {
            Logger.getLogger(DataSource.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void borrarNpc(Npc modelo) {
        StringBuffer query = new StringBuffer();
        query.append("DELETE FROM npcs WHERE 1=1");

        //Requisitos
        if (!modelo.getId().isEmpty()) {
            query.append(" AND id=" + escapeSql(modelo.getId()));
        }
        if (!modelo.getName().isEmpty()) {
            query.append(" AND name like \"%" + escapeSql(modelo.getName()) + "%\"");
        }
        if (!modelo.getProfesion().getId().isEmpty()) {
            query.append(" AND profesionid = " + escapeSql(modelo.getProfesion().getId()));
        }
        if (!modelo.getCiudad().getId().isEmpty()) {
            query.append(" AND ciudadid=" + escapeSql(modelo.getCiudad().getId()));
        }
        if (!modelo.getReino().getId().isEmpty()) {
            query.append(" AND reinoid=" + escapeSql(modelo.getReino().getId()));
        }
        if (modelo.getLugar().getX() != -1) {
            query.append(" AND x=" + escapeSql(String.valueOf(modelo.getLugar().getX())));
        }
        if (modelo.getLugar().getY() != -1) {
            query.append(" AND y=" + escapeSql(String.valueOf(modelo.getLugar().getY())));
        }
        if (modelo.getLugar().getZ() != -1) {
            query.append(" AND z=" + escapeSql(String.valueOf(modelo.getLugar().getZ())));
        }
        if (!modelo.getUserId().isEmpty()) {
            query.append(" AND userid=" + escapeSql(modelo.getUserId()));
        }
        query.append(";");
        //System.out.println(query.toString());
        try {
            executeStatement(query.toString());
        } catch (SQLException ex) {
            Logger.getLogger(DataSource.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void deploy() {
        try {
            Deployer deployer = new Deployer(this.getCurrentDatabaseFile());

            //Campo id, reciclable para otras tablas.
            Field id = new Field("id");
            id.addType(Field.IDFIELD);

            //Campo name, reciclable para otras tablas.
            Field name = new Field("name");
            name.addType(Field.STRING);

            //campos especiales de la tabla npcs
            Field profesionid = new Field("profesionid");
            profesionid.addType(Field.INTEGER);

            Field ciudadid = new Field("ciudadid");
            ciudadid.addType(Field.INTEGER);

            Field reinoid = new Field("reinoid");
            reinoid.addType(Field.INTEGER);

            Field userid = new Field("userid");
            userid.addType(Field.INTEGER);

            Field x = new Field("x");
            x.addType(Field.INTEGER);

            Field y = new Field("y");
            y.addType(Field.INTEGER);

            Field z = new Field("z");
            z.addType(Field.INTEGER);

            //crea las tablas
            Table npcs = new Table("npcs");
            npcs.addField(id, name, profesionid, ciudadid, reinoid, userid, x, y, z);

            Table profesiones = new Table("profesiones");
            profesiones.addField(id, name);

            Table ciudades = new Table("ciudades");
            ciudades.addField(id, name);

            Table reinos = new Table("reinos");
            reinos.addField(id, name);


            //las agrega al deployer
            deployer.addTable(npcs, profesiones, ciudades, reinos);

            //ejecuta las consultas
            deployer.deploy();

        } catch (IOException ex) {
            Logger.getLogger(DataSource.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}
